{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![alt text](https://github.com/callysto/callysto-sample-notebooks/blob/master/notebooks/images/Callysto_Notebook-Banner_Top_06.06.18.jpg?raw=true)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working With Open Data: Car Mileage Data Part 2\n",
    "\n",
    "This notebook  will use the same car mileage data set as in part one, but this time we will be demonstrating power of data aggregation. We will be introducing  pivot tables in Pandas, and exploring different aggregation functions. First lets reimport the libraries we need into this notebook, and download the data again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is the same as the previous notebook! We're just importing the data again\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt \n",
    "\n",
    "%matplotlib inline\n",
    "url = 'https://data.opendatasoft.com/explore/dataset/us-vehicle-fuel-economy-data-1984-2017@kapsarc/download/?format=csv&timezone=America/Denver&use_labels_for_header=true'\n",
    "car_data = pd.read_csv(url, sep=';')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Aggregating Data For More Informative Plots\n",
    "\n",
    "The car mileage data contains a temporal column (`year`). It is often enlightening to observe changes as a function of time. Unfortunately, this data is not currently organized in a way which makes plotting our data as a function of time immediately obvious. Using pandas we can use a pivot table to summarise and aggregate data quickly and easily. If you've used a pivot table in Excel before, the concept in pandas is identical.  Pivot tables are used and manipulated a little different within pandas. Let's work through a few examples and see if we can find anything more interesting within the data set using  a pivot table. \n",
    "\n",
    "## Making a Pivot table\n",
    "\n",
    "To make a pivot table, we don't need to do anything much more complicated than what we've already seen. We won't have to do any extra work or write hundreds of lines of code. As you'll find with most things you want to do in Python and Pandas: there's already a function that does exactly what you want. In the cell below we define create pivot table from our `car_data` dataframe using the aptly named `pivot_table` pandas function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "Here we're taking our car data data frame and aggregating it by year, below we explain what each argument does\n",
    "\n",
    "1. car_data          : Here we're simply passing our source data frame into the piviot table\n",
    "\n",
    "2. index = 'year'    : Here we're setting the index of our new piviot table. In essence this can be though of \n",
    "                       as both the 'index' i.e. what value labels each row uniquely, as well as the 'grouping'\n",
    "                       term. In this case, we'll see each row labeled by year, and each data point will be \n",
    "                       aggregated by which unique year it appeard under\n",
    "\n",
    "3. aggfunc = 'count' : This is our aggregation function. In this case we're simply choosing to count how many\n",
    "                       entries happen each year. However, there is no reason that we coudn't aggregate by other\n",
    "                       quantites such as the mean, median, mode, standard deviation and so on instead. \n",
    "                       \n",
    "4. Special Note      : You may have also noticed that we're not explicitly mentioning what to do with those 'NaN'\n",
    "                       values we noticed earlier. That is becasue our piviot table will automatically ignore any \n",
    "                       NaN values during the aggregation so we don't even have to think about it. Convenient!\n",
    "\n",
    "'''\n",
    "\n",
    "pd.pivot_table(car_data, \n",
    "                index = 'year',\n",
    "                aggfunc = 'count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Now that we have a pivot table, let's do something a little more interesting and see how the average fuel economy has changed over the time. We will aggregate (combine) our data by calculating the average (mean) fuel economy for all cars each year. As seen in the cell below, this can be accomplished in a single line of code using a pivot table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "Notice how this time we're not specifying an 'x' axes; this is because we're interested\n",
    "in plotting this by year, and as it's our index pandas will automatically use it for the unspecifed \n",
    "axes. \n",
    "'''\n",
    "\n",
    "pd.pivot_table(car_data,index='year', \n",
    "                        aggfunc = 'mean').plot(y = 'UHighway')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The resulting graphs from aggregation are starting to get more interesting! Around 2008 we start to see a distinct increase in average fuel economies of all vehicles. To get a better picture it is informative to also add some measure of variation to these plots. As we've aggregated by mean, it is prudent to also to include the mean plus and minus the standard deviation in this plot as well. We do  note that mean and standard deviation are  not the best metrics for data like this, as it is likely things are not normally distributed. For demonstration purposes like this, using mean and standard deviation for simplicity and exploration are more than adequate. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This time we're giving both our mean and standard deviation names that we can call later\n",
    "# Unfortunately, we're not very creative so we're simply calling the mean 'a' and standard\n",
    "# deviation 'b'.\n",
    "\n",
    "a = pd.pivot_table(car_data,index ='year', \n",
    "                   aggfunc = 'mean')\n",
    "\n",
    "b = pd.pivot_table(car_data,index ='year', \n",
    "                   aggfunc = 'std')\n",
    "\n",
    "# Here we're naming our plot 'ax' for axes \n",
    "\n",
    "my_plot = a.plot(y = 'UHighway', label=\"Mean\", figsize = (12,8))\n",
    "\n",
    "\n",
    "'''\n",
    "There's a lot going on in the two lines of code below, so let's take a moment to understand them\n",
    "\n",
    "1. When we type (a + b), we're actually adding our dataframes together element-by-element. This is \n",
    "   possible because a and b have identical column names and sizes. When we wrap them in parenthesis,\n",
    "   the .plot command will see that as a single data frame. We could have also specified something like\n",
    "       \n",
    "       Plus_one_sd = a + b\n",
    "       Plus_one_sd.plot( ... )\n",
    "    \n",
    "    However, sometimes it's easier to just do things in a single line. \n",
    "\n",
    "2. Also specifying ax=my_plot. This tells Python to place these new traces on the same picture as \n",
    "   'my_plot', or the plot of the mean we did in the line before this. \n",
    "\n",
    "3. By just doing mean +/- standard deviation, we are assuming symmetric uncertainties, and this may not\n",
    "   be the case in the actual data. But, for our purposes, it's likely more than adequate. \n",
    "'''\n",
    "\n",
    "(a + b).plot(ax = my_plot, y = 'UHighway', label = \"Mean + sd\") \n",
    "(a - b).plot(ax = my_plot, y= 'UHighway', label = \"Mean - sd\")\n",
    "\n",
    "plt.title(\"Average Fuel Economy\", size = 20)        \n",
    "plt.xlabel(\"Year\" , size = 16) \n",
    "plt.ylabel(\"Highway Miles Per Gallon\", size = 16)     \n",
    "plt.xticks(size = 14)                             \n",
    "plt.yticks(size = 14)                              \n",
    "plt.grid('on')                                     \n",
    "plt.autoscale(tight = True)  \n",
    "plt.legend(fontsize = 14) # Change font size of legend"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we aggregate data and observe the resulting variations, a much more interesting picture begins to emerge.  2010 began an upward trend of better average fuel efficiency which peaked in 2017 before starting to decrease . In 2019 (the most recent data) the standard deviation of the data set shows significant decrease. This implies that there is less variation in the data for 2019 car models. There could be several reasons for both this trend and the 'weirdness' of 2019. Perhaps there are more vehicles with larger engines being made in 2019? Perhaps there's missing data? Or perhaps something entirely different. There are many potential reasons. Let us see if there's a stronger argument than speculation by using some of the skills we've used already (and by introducing one more)\n",
    "\n",
    "## Deeper Analysis\n",
    "\n",
    "As we're looking for fuel efficiency, the most telltale sign of a more or less fuel efficient vehicle is the number of cylinders in the engine. An engine with more cylinders will use more gas than an engine with less. In this case, one of the things we can do is to group our data by _both_ year and the number of cylinders. This is done in the code below. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "double_grouping = pd.pivot_table(car_data, \n",
    "                index = ['year', 'cylinders'],  # Here we specify that we want to index (group) our data\n",
    "                                                # by year *and* number of cylinders\n",
    "                aggfunc = 'count')              # Aggregate by count\n",
    "\n",
    "double_grouping.head(10)                         # .head(10) wil only display the first 10 rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Where we see that we now have two groups: First our data is grouped by year, and then it is grouped by the number of cylinders in the engine. Unfortunately this dataframe is not currently in a form that our plotting library knows how to deal with; we have two column indexes, the number of cylinders and years meaning that our graphing library won't know how to treat the data. Luckily however, we can convert it to a form that the graphing will enjoy more using the `unstack()` function which will 'pivot' our pivot table such that it will be easier to plot. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "Here the unstack() function with no arguments in its parenthesis pivot our pivot table \n",
    "into a form that can be more easily plotted.\n",
    "'''\n",
    "\n",
    "double_grouping.unstack().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Where this is now in a form that we can plot. It might be interesting to first visualize the number of cars produced with various numbers of cylinders per year, which is done below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "Note we're doing all the previously shown manipulations in one line instead of using the variable we created \n",
    "earlier for illustrative purposes. This simply because it's more convenient to work with like this when you're\n",
    "potentialy changing what values you're sorting/how your aggregating. \n",
    "'''\n",
    "\n",
    "pd.pivot_table(car_data, \n",
    "                index = ['year', 'cylinders'],\n",
    "                aggfunc = 'count').unstack().plot(kind='line', y = \"model\", figsize = (16,8))\n",
    "\n",
    "plt.title(\"Engine Size Through the Years\", size = 20)        \n",
    "plt.xlabel(\"Year\" , size = 16) \n",
    "plt.ylabel(\"Total Number of Vehicles\", size = 16)     \n",
    "plt.xticks(size = 14)                             \n",
    "plt.yticks(size = 14)                              \n",
    "plt.grid('on')                                     \n",
    "plt.autoscale(tight=True)  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ah ha! So our decreasing standard deviation in 2019 is not in fact not 'real' - it is simply an artifact of a lack of data in 2019. More interestingly, is that starting from 2009, there is a noticeable increase in the number of four cylinder engines. Could this be the reason for the increase in mean highway fuel economy? Let's find out by instead aggregating by the mean for each engine type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(car_data, \n",
    "                index = ['year', 'cylinders'],\n",
    "                aggfunc = 'mean').unstack().plot(kind='line', y = \"UHighway\", figsize = (16,8))\n",
    "\n",
    "plt.title(\"Mean Fuel Economy of Engine Size Through the Years\", size = 20)        \n",
    "plt.xlabel(\"Year\" , size = 16) \n",
    "plt.ylabel(\"Mean Fuel Economy\", size = 16)     \n",
    "plt.xticks(size = 14)                             \n",
    "plt.yticks(size = 14)                              \n",
    "plt.grid('on')                                     \n",
    "plt.autoscale(tight=True)  \n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Interesting. By comparing the two plots above, it become apparent that while almost every engine type has gotten more fuel efficient on average, these gains in efficiency are minor. Only about 10 MPG. What's more interesting is that there are more four cylinder vehicles being produced since 2010. This is possibly evidence of a shift towards demand for more fuel efficient vehicles. It is possible to quantify which vehicles are contributing most to this gain in fuel efficiency. That analysis requires normalization of the data, and is left for another time. Regardless, using the skills you've already learned in this notebook  filtering  and aggregating data tables a within a pivot table, you could start your own analysis comparing which car manufacturers are creating the most fuel efficient vehicles. Or, you could explore any aspect you find interesting. \n",
    "\n",
    "# Conclusion\n",
    "\n",
    "In this notebook we walked through a few more 'advanced' examples of how to work with large open data sets like this. We used the power of pivot tables to aggregate our data by both counts and mean. We showed you some powerful tools at your disposal to explore vast amounts of data to start to draw conclusions from the data set. The skills you have learned here can be translated to other projects where aggregation of the data may prove to be invaluable."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![alt text](https://github.com/callysto/callysto-sample-notebooks/blob/master/notebooks/images/Callysto_Notebook-Banners_Bottom_06.06.18.jpg?raw=true)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
